﻿using System;
using Npgsql;
using PureMVC.Interfaces;
using PureMVC.Patterns;
using SMS.Core.Model.Data;
using SMS.Core.Model.Util;
using System.Collections.Generic;
using System.Data;

namespace SMS.Core.Model
{
    public class LoginProxy : Proxy, IProxy
    {
        #region Constants

        public new const string NAME = "LoginProxy";

        private static string NO_CONNECTION = "Can't log in to the system. The application is not connected to the PostgreSQL database.";

        #region Queries

        private const string QUERY_CHK_USERNAME = 
            @"SELECT username 
              FROM   staff 
              WHERE  username = @username";
        private const string QUERY_LOGIN = 
            @"SELECT username
              FROM   staff
              WHERE  username = @username AND pass_md5 = @password";
        private const string QUERY_FETCH_NAME = 
            @"SELECT first_name || ' ' || last_name
              FROM   staff
              WHERE  username = @username";
        
        #endregion

        #endregion

        #region Constructors

        public LoginProxy(NpgsqlConnection conn)
            : base(NAME)
        {
            this.conn = conn;
            InitializeProxy();
        }

        #endregion

        #region Members

        private NpgsqlConnection conn;

        #endregion

        #region Public Methods

        /// <summary>
        /// Makes a login attempt against the Shop Management System database.
        /// </summary>
        /// <param name="username">A username entered by a user.</param>
        /// <param name="password">A user's password.</param>
        /// <param name="conn"></param>
        public void Login(string username, string password)
        {
            // Bail out if not connected
            if (conn.State == ConnectionState.Closed ||
                conn.State == ConnectionState.Broken)
            {
                throw new Exception(NO_CONNECTION);
            }
            else if (conn.State == ConnectionState.Open)
            {
                // Check if the username is valid.
                NpgsqlCommand command = new NpgsqlCommand(QUERY_CHK_USERNAME, conn);
                command.Parameters.Add("username", username);
                if (command.ExecuteScalar() == null)
                {
                    throw new LoginException(username);
                }
                else
                {
                    // Encrypt password.
                    password = MD5Converter.GetMD5Hash(password);
                    // Build Command
                    command = new NpgsqlCommand(QUERY_LOGIN, conn);
                    command.Parameters.Add("username", username);
                    command.Parameters.Add("password", password);
                    if (command.ExecuteScalar() == null)
                    {
                        throw new LoginException(username, password);
                    }
                    else
                    {
                        // username/password combo is valid. Transmit user's name.
                        command = new NpgsqlCommand(QUERY_FETCH_NAME, conn);
                        command.Parameters.Add("username", username);
                        var name = (string)command.ExecuteScalar();
                        SendNotification(ModelConstants.UserAccount.LOGIN_SUCCESS, name);
                    }
                }
            }
        }

        #endregion

        #region Properties

        #endregion

        #region Private & Internal Methods


        private void InitializeProxy()
        {
            
        }

        #region Event Handlers


        #endregion

        #endregion
    }
}
